package com.demo.test;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Random;

public class sqlTestInsert {


  /*  public static void main(String[] args) {
        List<String> extracted = extracted(2020);
        System.err.println(extracted);

    }
*/
    private static List<String> extracted(int year) {
        // 设置年份为2020
        //int year = 2020;
        List<String> dateList = new ArrayList<>();
        // 创建Calendar对象
        Calendar calendar = Calendar.getInstance();

        // 设置年份
        calendar.set(Calendar.YEAR, year);
        String date = "";
        // 循环输出每一天的日期
        for (int month = 0; month < 12; month++) {
            for (int day = 1; day <= calendar.getActualMaximum(Calendar.DAY_OF_MONTH); day++) {
                // 设置月份和日期
                calendar.set(Calendar.MONTH, month);
                calendar.set(Calendar.DAY_OF_MONTH, day);

                // 格式化输出日期
                date = String.format("%04d%02d%02d", calendar.get(Calendar.YEAR),
                        calendar.get(Calendar.MONTH) + 1, calendar.get(Calendar.DAY_OF_MONTH));

                dateList.add(date);
                //break;
            }
             //System.err.println(dateList);

        }
        return dateList;

    }


    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
             // 关闭自动提交事务，改为手动提交
            connection.setAutoCommit(false);
            System.out.println("===== 开始插入数据 =====");
            long startTime = System.currentTimeMillis();
            //String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";
            String  sqlInsert ="INSERT INTO  zq_test_data_kpi(part_org, etl_dt, org_code, org_name, cust_type, pro_type, price_level, risk_level, purchase_amount, sale_amount, income, prod_id) VALUES ('801', ?, ?, '琢学证券', '1', '1', '8', '1', 366425.90, 1911726, 73848, '9')";
            System.out.println("===== 开始插入数据 =====" +sqlInsert);
            preparedStatement = connection.prepareStatement(sqlInsert);

            List<String> extracted = extracted(2021);
            for (int j = 0; j < extracted.size(); j++) {
                String s = extracted.get(j);
                for (int i = 1; i <= 10000; i++) {
                    //String startDate = "2020-02-01";
                    preparedStatement.setString(1, s);
                    //String number = String.valueOf((Math.random() * 9 + 1) * 100000);
                    Random random = new Random();
                    preparedStatement.setString(2, String.valueOf(random.nextInt(100000)));

                    // 添加到批处理中
                    preparedStatement.addBatch();

                    if (i % 10000 == 0) {
                        // 每1000条数据提交一次
                        preparedStatement.executeBatch();
                        connection.commit();
                        System.out.println("成功插入第 " + i + " 条数据");
                    }

                }
            }


            // 处理剩余的数据
            preparedStatement.executeBatch();
            connection.commit();
            long spendTime = System.currentTimeMillis() - startTime;
            System.out.println("成功插入 30 万条数据,耗时：" + spendTime + "毫秒");
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }



            }

        }




}
